package com.ray.controller.base;

import cn.dev33.satoken.annotation.SaCheckRole;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.ray.log.WebLog;
import com.ray.model.*;
import com.ray.util.JsonKit;
import com.ray.util.Ret;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;
import cn.hutool.core.util.StrUtil;

@RestController
@RequestMapping("dataObject")
@SaCheckRole("coder")
public class DataObjectController extends BaseController {

    @RequestMapping("getObjects")
    @WebLog(description = "获取元数据对象列表")
    public Ret getObjects(@RequestParam(defaultValue = "%%") String no) {
        List<DataObject> list = DataObject.dao.find("select * from data_object where no like ?", no);
        return Ret.ok("list", JsonKit.toJson(list));
    }

    @RequestMapping("getFieldsAndButtons")
    @WebLog(description = "获取元数据对象字段和按钮列表")
    public Ret getFieldsAndButtons(@RequestParam(defaultValue = "") String no) {
        List<DataField> fields = dataObjectService.getDataFields(no);
        List<DataButton> buttons = dataObjectService.getDataButtons(no);
        return Ret.ok("msg", "成功").setJson("fields", fields).setJson("buttons", buttons);
    }

    @RequestMapping("getDataSource")
    @WebLog(description = "获取数据源")
    public Ret getDataSource() {
        List<Record> list = new ArrayList<Record>();
        list.add(new Record().set("value", main));
        list.add(new Record().set("value", base));
        return Ret.ok("msg", "成功").setJson("list", list);
    }

    /**
     * @note 获取表空间所有的表名
     * @time 2024年03月24日 11:10:34
     * @author Ray
     */
    @RequestMapping("getTables")
    @WebLog(description = "获取表空间所有的表名")
    public Ret getTables(String data_source) {
        List<Record> list = Db.use(sys).find(
                "select table_name as value from tables where table_schema = '" + data_source + "'");
        return Ret.ok("msg", "成功").setJson("list", list);
    }

    /**
     * @note 新建元数据对象
     * @time 2024年03月24日 11:53:31
     * @author Ray
     */
    @RequestMapping("newObject")
    @WebLog(description = "新建元数据对象")
    @Transactional("baseTransactionManager")
    public Ret newObject(@RequestBody DataObject model) {
        DataObject exist = DataObject.dao.findFirst(
                "select * from data_object where no = ?", model.getNo());
        if (exist != null)
            return Ret.fail("msg", "新增元对象失败：编号已存在");
            // 生成元数据对象
        if (model.getWhereAttr() == null) {
            model.setWhereAttr("");
        }
        model.save();
        // 生成元数据对象字段
        List<Record> list = Db.use(sys)
                .find("select * from columns where table_schema = '" + model.getDataSource()
                        + "' and table_name = '" + model.getTableName() + "' order by ORDINAL_POSITION");
        for (int i = 0; i < list.size(); i++) {
            if (!"is_delete".equals(list.get(i).getStr("COLUMN_NAME")) &&
                    !"del_time".equals(list.get(i).getStr("COLUMN_NAME")) &&
                    !"del_user_id".equals(list.get(i).getStr("COLUMN_NAME"))) {
                DataField filed = new DataField();
                filed.setDataObjectNo(model.getNo());
                filed.setOrderNum(Integer.valueOf(list.get(i).getInt("ORDINAL_POSITION") + "0"));
                filed.setEn(list.get(i).getStr("COLUMN_NAME"));
                filed.setCn(list.get(i).getStr("COLUMN_COMMENT"));
                if ("int".equals(list.get(i).get("DATA_TYPE")) && !"PRI".equals(list.get(i).get("COLUMN_KEY"))) {
                    filed.setType("select");
                    // 是否生成字典
                    String comment = list.get(i).getStr("COLUMN_COMMENT");
                    String[] temp = comment.split(":");
                    if (temp.length == 2) {
                        filed.setCn(temp[0]);
                        String[] temp1 = temp[1].split(",");
                        for (int j = 0; j < temp1.length; j++) {
                            String[] dict = temp1[j].split("=");
                            Dicts dicts = new Dicts();
                            dicts.setField(filed.getEn());
                            dicts.setObject(model.getTableName());
                            dicts.setName(dict[1]);
                            dicts.setValue(Integer.valueOf(dict[0]));
                            dicts.save();
                        }
                    }
                    filed.setTypeConfig("from #datasource_base.dicts where object='" + model.getTableName() + "' and field = '"
                            + filed.getEn() + "'|name|value");
                } else if ("tinyint".equals(list.get(i).get("DATA_TYPE"))) {
                    filed.setType("switch");
                    filed.setTypeConfig("from table_name where 1=1|label|value");
                } else if ("date".equals(list.get(i).get("DATA_TYPE"))) {
                    filed.setType("date");
                } else if ("datetime".equals(list.get(i).get("DATA_TYPE"))) {
                    filed.setType("datetime");
                }
                //是否为空默认加上前端校验
                if ("NO".equals(list.get(i).get("IS_NULLABLE"))) {
                    filed.setValidator("[{ required: true, message: '请输入" + filed.getCn() + "', trigger: 'blur' }]");
                }
                //默认字段为无法新增、无法修改
                if ("id".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "region".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "create_user_id".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "create_user_name".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "create_time".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "update_time".equals(list.get(i).getStr("COLUMN_NAME"))) {
                    filed.setIsAdd(false);
                    filed.setIsUpdate(false);
                    filed.setIsLineUpdate(false);
                }
                if ("id".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "region".equals(list.get(i).getStr("COLUMN_NAME"))
                        || "create_user_id".equals(list.get(i).getStr("COLUMN_NAME"))) {
                    filed.setIsShow(false);
                }
                filed.save();
            }
        }
        return Ret.ok("msg", "新建模型成功");
    }

    /**
     * @note 更新元对象
     * @time 2024年03月24日 12:04:29
     * @author Ray
     */
    @RequestMapping("updateObject")
    @WebLog(description = "更新元对象")
    @Transactional("baseTransactionManager")
    public Ret updateObject(@RequestBody DataObject model) {
        // 已有字段
        List<DataField> hasList = dataObjectService.getDataFields(model.getNo());
        // 生成元数据对象字段
        List<Record> list = Db.use(sys)
                .find("select * from columns where table_schema = '" + model.getDataSource()
                        + "' and table_name = '" + model.getTableName() + "' order by ORDINAL_POSITION");
        for (int i = 0; i < list.size(); i++) {
            boolean flag = true;
            for (int j = 0; j < hasList.size(); j++) {
                if (list.get(i).getStr("COLUMN_NAME").equals(hasList.get(j).getEn())) {
                    flag = false;
                }
            }
            if (flag) {
                if (!"is_delete".equals(list.get(i).getStr("COLUMN_NAME")) &&
                        !"del_time".equals(list.get(i).getStr("COLUMN_NAME")) &&
                        !"del_user_id".equals(list.get(i).getStr("COLUMN_NAME"))) {
                    DataField filed = new DataField();
                    filed.setDataObjectNo(model.getNo());
                    filed.setOrderNum(Integer.valueOf(list.get(i).getInt("ORDINAL_POSITION") + "0"));
                    filed.setEn(list.get(i).getStr("COLUMN_NAME"));
                    filed.setCn(list.get(i).getStr("COLUMN_COMMENT"));
                    if ("int".equals(list.get(i).get("DATA_TYPE")) && !"PRI".equals(list.get(i).get("COLUMN_KEY"))) {
                        filed.setType("select");
                        // 是否生成字典
                        String comment = list.get(i).getStr("COLUMN_COMMENT");
                        String[] temp = comment.split(":");
                        if (temp.length == 2) {
                            filed.setCn(temp[0]);
                            String[] temp1 = temp[1].split(",");
                            for (int j = 0; j < temp1.length; j++) {
                                String[] dict = temp1[j].split("=");
                                Dicts dicts = Dicts.dao.findFirst("select * from dicts where field = '" + filed.getEn()
                                        + "'" + " and object = '" + model.getTableName() + "'" + " and name = '"
                                        + dict[1] + "'" + " and value = " + Integer.valueOf(dict[0]));
                                if (dicts == null) {
                                    dicts = new Dicts();
                                    dicts.setField(filed.getEn());
                                    dicts.setObject(model.getTableName());
                                    dicts.setName(dict[1]);
                                    dicts.setValue(Integer.valueOf(dict[0]));
                                    dicts.save();
                                }
                            }
                        }
                        filed.setTypeConfig("from #datasource_base.dicts where object='" + model.getTableName() + "' and field = '"
                                + filed.getEn() + "'|name|value");
                    } else if ("tinyint".equals(list.get(i).get("DATA_TYPE"))) {
                        filed.setType("switch");
                        filed.setTypeConfig("from table_name where 1=1|label|value");
                    } else if ("date".equals(list.get(i).get("DATA_TYPE"))) {
                        filed.setType("date");
                    } else if ("datetime".equals(list.get(i).get("DATA_TYPE"))) {
                        filed.setType("datetime");
                    }
                    //是否为空默认加上前端校验
                    if ("NO".equals(list.get(i).get("IS_NULLABLE"))) {
                        filed.setValidator("[{ required: true, message: '请输入" + filed.getCn() + "', trigger: 'blur' }]");
                    }
                    //默认字段为无法新增、无法修改
                    if ("id".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "region".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "create_user_id".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "create_user_name".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "create_time".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "update_time".equals(list.get(i).getStr("COLUMN_NAME"))) {
                        filed.setIsAdd(false);
                        filed.setIsUpdate(false);
                        filed.setIsLineUpdate(false);
                    }
                    if ("id".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "region".equals(list.get(i).getStr("COLUMN_NAME"))
                            || "create_user_id".equals(list.get(i).getStr("COLUMN_NAME"))) {
                        filed.setIsShow(false);
                    }
                    dataObjectService.dataFieldSave(filed);
                }
            }
        }
        return Ret.ok("msg", "更新元对象成功");
    }

    /**
     * @note 复制元对象
     * @time 2024年03月24日 16:58:40
     * @author Ray
     */
    @RequestMapping("copyObject")
    @WebLog(description = "复制元对象")
    @Transactional("baseTransactionManager")
    public Ret copyObject(@RequestBody JSONObject json) {
        DataObject model = JSON.parseObject(json.toJSONString(), DataObject.class);
        List<DataField> list = dataObjectService.getDataFields(model.getNo());
        model.remove("id");
        model.setNo(json.getString("new_no"));
        model.setCn(model.getCn() + "_copy");
        model.save();
        for (int i = 0; i < list.size(); i++) {
            list.get(i).remove("id");
            list.get(i).setDataObjectNo(model.getNo());
            list.get(i).save();
        }
        return Ret.ok("msg", "复制元对象成功");
    }

    /**
     * @note 修改元对象
     * @time 2024年03月24日 17:58:21
     * @author Ray
     */
    @RequestMapping("changeObject")
    @WebLog(description = "修改元对象")
    public Ret changeObject(@RequestBody DataObject model) {
        dataObjectService.dataObjectUpdate(model);
        return Ret.ok("msg", "修改成功");
    }

    /**
     * @note 删除元对象
     * @time 2024年03月24日 18:01:13
     * @author Ray
     */
    @RequestMapping("del")
    @WebLog(description = "删除元对象")
    public Ret del(String no) {
        dataObjectService.dataObjectDel(no);
        return Ret.ok("msg", "删除成功");
    }

    /**
     * @note 新增虚拟字段
     * @time 2024年03月24日 18:32:17
     * @author Ray
     */
    @RequestMapping("newField")
    @WebLog(description = "新增虚拟字段")
    public Ret newField(@RequestBody DataField model) {
        model.setIsFictitious(true);
        model.setIsUpdate(false);
        model.setIsLineUpdate(false);
        model.setIsAdd(false);
        dataObjectService.dataFieldSave(model);
        return Ret.ok("msg", "添加成功").setJson("row", model);
    }

    /**
     * @note 修改元对象字段
     * @time 2024年03月24日 18:34:44
     * @author Ray
     */
    @RequestMapping("changeFiled")
    @WebLog(description = "修改元对象字段")
    public Ret changeFiled(@RequestBody DataField model) {
        dataObjectService.dataFieldUpdate(model);
        return Ret.ok("msg", "修改成功");
    }

    /**
     * @note 删除字段
     * @time 2024年03月24日 18:36:34
     * @author Ray
     */
    @RequestMapping("delField")
    @WebLog(description = "删除字段")
    public Ret delField(@RequestBody DataField model) {
        dataObjectService.dataFieldDel(model);
        return Ret.ok("msg", "删除成功");
    }

    /**
     * @note 新增元对象按钮
     * @time 2024年03月24日 18:38:55
     * @author Ray
     */
    @RequestMapping("newButton")
    @WebLog(description = "新增元对象按钮")
    @Transactional("baseTransactionManager")
    public Ret newButton(@RequestBody DataButton model) {
        model.setPermissionNo(model.getDataObjectNo() + "_" + model.getPermissionNo());
        dataObjectService.dataButtonSave(model);
        //新增对应菜单权限列表
        List<Menu> menus = Menu.dao.find("select * from menu where data_object_no = ? or son_data_object_no = ?", model.getDataObjectNo(), model.getDataObjectNo());
        for (int i = 0; i < menus.size(); i++) {
            Permissions p = new Permissions();
            p.setNo(menus.get(i).getNo() + "_" + model.getPermissionNo()).setName(model.getPermissionName()).setType(2).setMenuNo(menus.get(i).getNo()).save();
        }
        return Ret.ok("msg", "添加成功");
    }
    
    /**
     * @note 修改元对象按钮
     * @time 2024年03月24日 18:40:33
     * @author Ray
     */
    @RequestMapping("changeButton")
    @WebLog(description = "修改元对象按钮")
    public Ret changeButton(@RequestBody DataButton model) {
        dataObjectService.dataButtonUpdate(model);
        return Ret.ok("msg", "修改成功");
    }
    
    /**
     * @note 删除元对象按钮
     * @time 2024年03月24日 18:41:20
     * @author Ray
     */
    @RequestMapping("delButton")
    @WebLog(description = "删除元对象按钮")
    public Ret delButton(@RequestBody DataButton model) {
        dataObjectService.dataButtonDel(model);
        return Ret.ok("msg", "删除成功");
    }

    /**
     * @note 导出元对象为sql（方便升级）
     * @time 2024年03月24日 18:46:21
     * @author Ray
     */
    @RequestMapping("exportModel")
    @WebLog(description = "导出元对象为sql")
    public Ret exportModel(int id) {
        StringBuffer sql = new StringBuffer();
        DataObject object = DataObject.dao.findById(id);
        sql.append(getInsertSql("data_object",JsonKit.toJson(object).toString()));
        sql.append("\n");
        List<DataField> fields = DataField.dao.find("select * from data_field where data_object_no = ?",object.getNo());
        for (int i = 0; i < fields.size(); i++) {
            sql.append(getInsertSql("data_field",JsonKit.toJson(fields.get(i)).toString()));
            sql.append("\n");
        }
        List<DataButton> buttons = DataButton.dao.find("select * from data_button where data_object_no = ?",object.getNo());
        for (int i = 0; i < buttons.size(); i++) {
            sql.append(getInsertSql("data_button",JsonKit.toJson(buttons.get(i)).toString()));
            sql.append("\n");
        }
        return Ret.ok("msg", "复制sql成功，请到旧项目数据库中粘贴并执行sql即可").set("sql", sql.toString());
    }
    
    public String getInsertSql(String table,String json) {
        JSONObject j = JSONObject.parseObject(json);
        String sql1 = "insert into "+table+" (";
        String sql2 = "values (";
        for (String key:j.keySet()) {
            if ("ID".equals(key) || "id".equals(key)){
                continue;   //id 自动生成无需手动插入
            }
            sql1+= key+",";
            if(j.getString(key)==null) {
                sql2+= j.get(key)+",";
            }else if("true".equals(j.getString(key))||"false".equals(j.getString(key))) {
                String temp = j.getBoolean(key)?"1":"0";
                sql2+= "'"+temp+"',";
            }else {
                String temp = j.get(key).toString().replaceAll("'", "\\\\'");
                sql2+= "'"+temp+"',";
            }
        }
        sql1 = StrUtil.removeSuffix(sql1, ",")+")";
        sql2 = StrUtil.removeSuffix(sql2, ",")+")";
        return sql1+" "+sql2+";";
    }

}
